using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.Odbc;

namespace DAO
{
    public class DauSachDAO
    {
        DataProvider _provider = new DataProvider();
        public DauSachDAO()
        {
            _provider.connect();
        }
        public static List<String> ListGenre()
        {
            DataProvider dataProvider = new DataProvider();
            dataProvider.connect();
            String sQuery = "SELECT DISTINCT TheLoai FROM DAUSACH ";
            IDataReader reader = dataProvider.executeQuery(sQuery);
            List<String> tmp = new List<String>();
            while (reader.Read())
                tmp.Add(reader[0].ToString());
            dataProvider.disconnect();
            return tmp;
        }
        static public List<DauSachDTO> Search(String ISBN, String author, String name, String genre)
        {
            DataProvider dataProvider = new DataProvider();
            dataProvider.connect();
            bool flg=false;
            String sQuery = "SELECT * FROM DAUSACH WHERE ";
            if (ISBN != "")
            {
                sQuery = sQuery + "ISBN='" + ISBN + "' AND ";
                flg = true;
            }
            if (author != "")
            {
                sQuery = sQuery + "TacGia='" + author + "' AND ";
                flg = true;
            }
            if (name != "")
            {
                sQuery = sQuery + "TenDauSach='" + name + "' AND ";
                flg = true;
            }
            if (genre != "")
            {
                sQuery = sQuery + "TheLoai='" + genre + "'";
                flg = true;
            }
            else
            {
                if (sQuery.EndsWith("AND "))
                    sQuery = sQuery.Substring(0, sQuery.Length - 4);
            }
            if(!flg)
                sQuery = sQuery.Substring(0, sQuery.Length - 6);
            IDataReader reader = dataProvider.executeQuery(sQuery);
            List<DauSachDTO> tmp = new List<DauSachDTO>();
            while (reader.Read())
            {
                DauSachDTO dauSachDTO = new DauSachDTO();
                dauSachDTO.ISBN = (String)reader[0];
                dauSachDTO.TenSach = (String)reader[1];
                dauSachDTO.TheLoai = (String)reader[2];
                dauSachDTO.TacGia = (String)reader[3];
                dauSachDTO.TinhTrang = (String)reader[4];
                dauSachDTO.TomTat = (String)reader[5];
                tmp.Add(dauSachDTO);
            }
            dataProvider.disconnect();
            return tmp;
        }
        public DataTable GetTableDauSach()
        {            
            String stringSqlQrery = "Select * from DauSach";
            OdbcDataAdapter da = _provider.OdbcAdapter(stringSqlQrery);
            DataSet ds = new DataSet();
            da.Fill(ds);
            _provider.disconnect();
            return ds.Tables[0];                          
        }

    }
}
